﻿

Imports System.IO
Imports System.Data.SqlClient

'Auther : Jackson Thomas
'Date : 23 February 2012
'Description: This class contains some common functions/procedures can use globally in our SERPS project.
'The purpose of functions/procedures of this class is to decrease repetitiosn of coding.

Public Class clsGlobalUtility

    'Here creating an object for a class that class providing an sql connection.
    Dim connection As New clsSqlConnection


    'This Function is used to fetch all financial year months in order of financial year and fill in combobox.
    'Binding all months in a datatable here, then it returned.we can call this function and set it to as 
    'datasource of a combobox.
    Public Function tofetchFinancialYearMonth() As DataTable

        Dim datatable As New DataTable
        Dim sqldatareader As SqlDataReader
        Dim sqlcommand As New SqlCommand

        Try
            sqlcommand.Connection = connection.toOpen
            sqlcommand.CommandType = CommandType.StoredProcedure
            sqlcommand.CommandText = "fetchFinancialYearMonth"

            sqldatareader = sqlcommand.ExecuteReader
            datatable.Load(sqldatareader)

        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Critical, "SERPS")
        Finally
            connection.toClose()
        End Try

        Return datatable

    End Function

    Public Function tofetchProductWorld() As DataTable

        Dim datatable As New DataTable
        Dim sqldatareader As SqlDataReader
        Dim sqlcommand As New SqlCommand

        Try
            sqlcommand.Connection = connection.toOpen
            sqlcommand.CommandType = CommandType.StoredProcedure
            sqlcommand.CommandText = "fetchProductWorld"

            sqldatareader = sqlcommand.ExecuteReader
            datatable.Load(sqldatareader)
        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Critical, "SERPS")
        Finally
            connection.toClose()
        End Try

        Return datatable

    End Function


    Public Sub toExportDataGridViewToExcel(ByVal gridViewId As DataGridView,  ByVal FilePath As String)


        ' creating Excel Application
        Dim ExcelApp As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()


        ' creating new WorkBook within Excel application
        Dim workbook As Microsoft.Office.Interop.Excel._Workbook = ExcelApp.Workbooks.Add(Type.Missing)


        ' creating new Excelsheet in workbook
        Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing

        ' see the excel sheet behind the program
        'app.Visible = True

        ' get the reference of first sheet. By default its name is Sheet1.
        ' store its reference to worksheet
        worksheet = workbook.Sheets("Sheet1")
        worksheet = workbook.ActiveSheet

        ' changing the name of active sheet
        worksheet.Name = "Exported From List"


        ' storing header part in Excel
        For i As Integer = 1 To gridViewId.Columns.Count
            worksheet.Cells(1, i) = gridViewId.Columns(i - 1).HeaderText
        Next



        ' storing Each row and column value to excel sheet
        For i As Integer = 0 To gridViewId.Rows.Count - 2
            For j As Integer = 0 To gridViewId.Columns.Count - 1
                worksheet.Cells(i + 2, j + 1) = gridViewId.Rows(i).Cells(j).Value.ToString()
            Next
        Next



        ' save the application
        workbook.SaveAs(FilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

        ' Exit from the application
        ExcelApp.Quit()

    End Sub



End Class
